package com.bestom.waterquality.sql;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.bestom.waterquality.entity.Constant;
import com.bestom.waterquality.util.SharedPreferencesUtil;

import java.io.File;

/**
 * @author shulman
 */
public class DbManage {

    protected static DbManage _instance;
    private SQLiteDatabase db;

    public DbManage(Context context){
        db = new DbHelper(context).getWritableDatabase();
    }

    public static DbManage getInstance(Context context){
        if (_instance == null) _instance = new DbManage(context);
        return _instance;
    }

    public SQLiteDatabase getDb() {
        return db;
    }


    /**
     * 功能           往预警记录表中添加数据
     * @param time    记录时间
     * @param data    data数值
     * @param path    图片存放路径
     */
    public void addWarningRecord(String time, String data, String path){
        db.execSQL("insert into warning_record(time,warning_data,image_path) values(?,?,?)", new String[] { time, data, path });
    }

    /**
     * 功能           往实时记录表中添加数据
     * @param time    记录时间
     * @param vcp     vcp数值
     * @param tmp     tmp数值
     * @param path    图片存放路径
     */
    public void addRealRecord(String time, String vcp, String tmp, String path){
        db.execSQL("insert into real_record(time,real_vcp,real_tmp,image_path) values(?,?,?,?)", new String[] { time, vcp, tmp, path });
    }

    /**
     * 功能   获取该数据表的总行数
     */
    public long getDataCount(String tableName){
        Cursor cursor = db.rawQuery("select count(*) from " + tableName, null);
        cursor.moveToFirst();
        long count = cursor.getLong(0);
        cursor.close();
        return count;
    }

    /**
     * 功能                 定位起查询num条数据
     * @param tableName    表名
     * @param num          查询条数
     * @param start        从哪一行开始往后查
     * @return             返回包含结果的 Cursor
     */
    public Cursor select(String tableName, int num, int start){
        return db.rawQuery("select * from " + tableName + " limit ? offset ?", new String[] { String.valueOf(num), String.valueOf(start) });
    }

    /**
     * 功能   降序查询数据表
     */
    public Cursor select(String tableName){
        return db.rawQuery("select * from " + tableName + " order by _id desc",null);
    }

    /**
     * 功能          降序查询num条数据
     * @param num    要查询的条数
     */
    public Cursor select(String tableName, int num){
        return db.rawQuery("select * from " + tableName + " order by _id desc limit 0," + num,null);
    }

    /**
     * 查询实时记录的三类数据，并组合，为发送至 MQTT
     * */
    public StringBuilder getToMtData(){

        StringBuilder str = new StringBuilder();
        Cursor cursor1 = select(DbHelper.ORP_Record_NAME, 1);
        while (cursor1.moveToNext()){
            Log.d("SQL","ORP" + cursor1.getString(cursor1.getColumnIndex("data")));
            str.append("ORP:").append(cursor1.getString(cursor1.getColumnIndex("data")));
        }
        cursor1.close();

        Cursor cursor2 = select(DbHelper.PH_Record_NAME, 1);
        while (cursor2.moveToNext()){
            Log.d("SQL","PH" + cursor2.getString(cursor2.getColumnIndex("data")));
            str.append(",PH:").append(cursor2.getString(cursor2.getColumnIndex("data")));
        }
        cursor2.close();

        Cursor cursor3 = select(DbHelper.VCP_Record_NAME, 1);
        while (cursor3.moveToNext()){
            Log.d("SQL","VCP" + cursor3.getString(cursor3.getColumnIndex("data")));
            str.append(",VCP:").append(cursor3.getString(cursor3.getColumnIndex("data")));
//            str.append("---" + Constant.realImageDir).append(cursor3.getString(cursor3.getColumnIndex("image_path")));

        }
        cursor3.close();
        return str;
    }

    /**
     * 分别查询当天各类数据所有数据，并求该类数据的平均值
     * @param tableName  查询的表名
     * @param field      查询的字段
     * @param time       查询的时间
     * @return           字符串形式该天平均值
     */
    public String selectTodayAvg(String tableName, String field, String time){
        Cursor cursor = db.rawQuery("select sum(?) as sum_value, avg(?) as avg_value from " + tableName + " where time like ?", new String[]{ field, field, time});
        String avg = "";
        while (cursor.moveToNext()){
            String sum_value = cursor.getString(cursor.getColumnIndex("sum_value"));
            String avg_value = cursor.getString(cursor.getColumnIndex("avg_value"));
            if (sum_value == null || sum_value.isEmpty()) return "0.0f";
            avg = String.valueOf(Float.parseFloat(avg_value) / Float.parseFloat(sum_value));
        }
        cursor.close();
        return avg;
    }
    public void addAvg(String tableName, String time, String data){
        db.execSQL("insert into " + tableName + "(time,data) values(?,?)", new String[] { time, data });
    }

    public void addORPOrPHOrVcpData(String tableName, String time, String data){
        db.execSQL("insert into " + tableName + "(time,data) values(?,?)", new String[] { time, data });
    }


    /**
     * 功能           删除预警记录表中某一天的数据
     * @param time    记录时间
     */
    public void deleteWarningData(String time){
        db.execSQL("delete from warning_record where time like ?",new String[]{time + "%"});
    }


    /**
     * 功能           删除实时记录表中某一天的数据
     * @param time    记录时间
     */
    public void deleteRealData(String time){
        db.execSQL("delete from real_record where time like ?",new String[]{time + "%"});
    }

    /**
     * 功能           删除记录表中某一天的数据并删除图库中对应的图片
     * @param time    记录时间
     */
    public void deleteDataAndImage(String time){
        if (time.isEmpty()) return;
        Cursor cursor1 = db.rawQuery("select * from real_record where time like ?",new String[]{time + "%"});
        while (cursor1.moveToNext()){
            String path = cursor1.getString(cursor1.getColumnIndex("image_path"));
            if (!path.isEmpty()){
                File file = new File(Constant.realImageDir + path);
                if (file.exists()) {
                    file.delete();
                }
            }
        }
        cursor1.close();

        deleteRealData(time);

        Cursor cursor2 = db.rawQuery("select * from real_record where time like ?",new String[]{time + "%"});
        while (cursor2.moveToNext()){
            String path = cursor2.getString(cursor2.getColumnIndex("image_path"));
            if (!path.isEmpty()){
                File file = new File(Constant.warningImageDir + path);
                if (file.exists()) {
                    file.delete();
                }
            }
        }
        cursor2.close();

        deleteWarningData(time);

    }

    /**
     * 求近各项数据90天的平均值作为警戒值原始值
     */
    public void updateAvg(){
        Cursor cursor1 = db.rawQuery("select sum(data) as sum_value, avg(data) as avg_value from orp_avg order by _id desc limit 0,90", null);
        while (cursor1.moveToNext()){
            String sum_value = cursor1.getString(cursor1.getColumnIndex("sum_value"));
            String avg_value = cursor1.getString(cursor1.getColumnIndex("avg_value"));
            if (sum_value == null || sum_value.isEmpty()) break;
            SharedPreferencesUtil.getInstance().putValue(Constant.ORPAvg, Float.parseFloat(avg_value) / Float.parseFloat(sum_value));
        }
        cursor1.close();
        Cursor cursor2 = db.rawQuery("select sum(data) as sum_value, avg(data) as avg_value from ph_avg order by _id desc limit 0,90",null);
        while (cursor2.moveToNext()){
            String sum_value = cursor2.getString(cursor2.getColumnIndex("sum_value"));
            String avg_value = cursor2.getString(cursor2.getColumnIndex("avg_value"));
            if (sum_value == null || sum_value.isEmpty()) break;
            SharedPreferencesUtil.getInstance().putValue(Constant.PHAvg, Float.parseFloat(avg_value) / Float.parseFloat(sum_value));
        }
        cursor2.close();
        Cursor cursor3 = db.rawQuery("select sum(data) as sum_value, avg(data) as avg_value from vcp_avg order by _id desc limit 0,90",null);
        while (cursor3.moveToNext()){
            String sum_value = cursor3.getString(cursor3.getColumnIndex("sum_value"));
            String avg_value = cursor3.getString(cursor3.getColumnIndex("avg_value"));
            if (sum_value == null || sum_value.isEmpty()) break;
            SharedPreferencesUtil.getInstance().putValue(Constant.VCPAvg, Float.parseFloat(avg_value) / Float.parseFloat(sum_value));
        }
        cursor3.close();
    }
}
